# -*- coding:utf-8 -*-
'''
Created on 2010-10-4

查询数据库，得到统计数据。

@author: lijun
'''

from kspiderapp.dbUtil import CreateSelectSql,SelectDB,GetSelectSql
import conf

def GetUserCount(beginDateStr, endDateStr):
    '''得到指定时间段用户数量
    '''
    sql = CreateSelectSql(fields='count(distinct user) as user_count', 
                          table=conf.FILEINFO_TABLE,
                          wheres="date_time >= '%s' and date_time < '%s'" % (beginDateStr, endDateStr),
			  ext="order by date_time ")
    datas = SelectDB(sql)
    count = datas[0][0]
    return count if count != None else 0
    

def GetFileInfo(beginDateStr, endDateStr):
    '''得到文件大小和文件数量
    '''
    sql = CreateSelectSql(fields='sum(file_size), sum(file_count)', 
                          table=conf.FILEINFO_TABLE, 
                          wheres="date_time >= '%s' and date_time < '%s'" % (beginDateStr, endDateStr) )
    datas = SelectDB(sql)
    size = datas[0][0]
    count =  datas[0][1]
    return size, count


def GetActionCount(beginDateStr, endDateStr):
    '''得到指定时间段内操作数
    '''
    sql = CreateSelectSql(fields='count(id), action', 
                          table=conf.ACTIONINFO_TABLE, 
                          wheres="date_time >= '%s' and date_time < '%s'" % (beginDateStr, endDateStr),
                          ext='group by action order by date_time' )
    datas = SelectDB(sql)
    actionCount = {}
    for data in datas:
        count = int(data[0])
        action = data[1]
        actionCount[action] = count
        
    return actionCount
    
def GetUserCountByRange(beginDateStr, endDateStr, dateFormat):
    '''查询得到时间段内用户数量，返回key为日期，value为用户数的字典。
    '''
    
    selectItems = ('count(distinct user) as user_count',
                   '(DATE_FORMAT(date_time, \"%s\")) as date_time_format' % dateFormat
                     )
    froms = conf.FILEINFO_TABLE
    where = "date_time >= '%s' and date_time < '%s' " %(beginDateStr, endDateStr)
    ext = 'group by date_time_format order by date_time'  
    sql = GetSelectSql(selectItems, froms, where, ext)
    
    datas = SelectDB(sql)
    dateCount = {}
    for data in datas:
        count = int(data[0])
        date = data[1]
        dateCount[date] = count
        
    return dateCount
    
    
def GetFileCountSizeByRange(beginDateStr, endDateStr, dateFormat):
    '''查询得到时间范围内，分段时间内的文件大小和文件数
    '''
    selectItems = ('(DATE_FORMAT(date_time, \"%s\")) as date_time_format' % dateFormat,
                   'sum(file_size) as file_sise_sum ',
                   'sum(file_count) as file_count_sum'
                     )
    
    subSelectItems = ('*')
    subFroms = conf.FILEINFO_TABLE
    subWhere = "date_time >= '%s' and date_time < '%s' " % (beginDateStr, endDateStr)
    subExt = 'order by date_time desc'
    subSql = GetSelectSql(subSelectItems, subFroms, subWhere, subExt)
    
#    froms = '(select * from %s  order by date_time desc) temp' % conf.FILEINFO_TABLE
    froms = '(' + subSql + ') ' + 'temp' 
    ext = 'group by date_time_format order by date_time'  
    sql = GetSelectSql(selectItems, froms, None, ext)
    
    datas = SelectDB(sql)
    dateSizeCount = {}
    for data in datas:
        date = data[0]
        size = int(data[1])
        count = int(data[2])
        sizeCount = {'size':size, 'count':count}
        dateSizeCount[date] = sizeCount
        
    return dateSizeCount
    
    


#def GetCountInfoSql(beginDate, endDate, table, dateFormat):
#          
#    selectItems = ('count(id)', '(DATE_FORMAT(date_time, \"%s\")) as date_time_format' % dateFormat
#                     )
#    froms = table
#    where = "date_time >= '%s' and date_time < '%s' " %(beginDate, endDate)
#    ext = 'group by date_time'  
#    sql = GetSelectSql(selectItems, froms, where, ext)
#    return sql
#    
#    
#
#def GetSumInfoSql(beginDate, endDate, host, table, dateFormat):
#    
#    countSql = GetCountInfoSql(beginDate, endDate, host, table, dateFormat)
#    selectItems = ('server2','date_time_format','type2','view2', 'sum(time_sum)/sum(code_count) as time_avg', 'code2',
#                    'sum(code_count) as code_count_sum' )
#    froms = '(%s) as t2' % countSql
#    ext = "group by server2, code2, date_time_format, view2, type2"
#    sql = GetSelectSql(selectItems, froms, None, ext)
#    return sql 
    
    
    
    
    
if __name__ == '__main__':
    beginDateStr = '2010-10-15'
    endDateStr = '2010-10-17'
#    count = GetUserCount(beginDateStr, endDateStr)
#    print count
#
#    size, count = GetFileInfo(beginDateStr, endDateStr)
#    print size, count
#
#    actionCount = GetActionCount(beginDateStr, endDateStr)
#    print actionCount

    '''select sum(user_count), date from (select count(distinct user) as user_count, DATE_FORMAT(date_time, '%Y-%m-%d') as date from kspiderapp_actioninfo group by date_time ) as t group by date;

    '''

    dateCount = GetUserCountByRange(beginDateStr, endDateStr, dateFormat='%Y-%m-%d %H:00:00')
#    print dateCount
    
    dateSizeCount = GetFileCountSizeByRange(beginDateStr, endDateStr, dateFormat='%Y-%m-%d %H:00:00')
    print dateSizeCount



